<?php
include_once("Constants.php");
include_once("Entity/survey_answer.php");

class survey_answer_dao_base {

	function delete_survey_answer($id)
	{
		try
		{
			$dbh = new PDO(Constants::$connectionString, Constants::$username, Constants::$password);
			$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$dbh->beginTransaction();

			$stmt = $dbh->prepare("DELETE FROM survey_answer WHERE survey_answer_id = :survey_answer_id");
			$stmt->bindParam(':survey_answer_id', $id, PDO::PARAM_INT);
			$stmt->execute();

			$dbh->commit();
			$dbh = null;
		}
		catch(PDOException $e)
		{
			echo '<br />' . $e->getMessage();
			$dbh->rollback();
		}
	}



	function count_user_survey($survey_id){
		try
		{
			$dbh = new PDO(Constants::$connectionString, Constants::$username, Constants::$password);
			$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$dbh->beginTransaction();

			$stmt = $dbh->prepare("select count(distinct sa_u.user_id) as counter from survey_answer_user sa_u,survey_answer sa,survey_question sq where ".
								" sa_u.survey_answer_id = sa.survey_answer_id and sa.survey_question_id = sq.survey_question_id ".
								" and  sq.survey_id =:survey_id");
			$stmt->bindParam(':survey_id', $survey_id);
			$stmt->execute();
			$s =null;
			$rows = $stmt->fetch();
			$s = $rows['counter'];
			$dbh->commit();
			$dbh = null;

			return $s;
		}
		catch(PDOException $e)
		{
			echo '<br />' . $e->getMessage();
			$dbh->rollback();
		}

	}

	function get_survey_answer_list_by_user_survey($user_id,$survey_id){
		try
		{
			$dbh = new PDO(Constants::$connectionString, Constants::$username, Constants::$password);
			$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$dbh->beginTransaction();

			$aos = array();
			$stmt = $dbh->prepare("SELECT * FROM survey_answer where survey_answer_id in (".
			 					"select sa.survey_answer_id from survey_answer_user sa_u,survey_answer sa,survey_question sq where sa_u.user_id=:user_id".
								" and sa_u.survey_answer_id = sa.survey_answer_id and sa.survey_question_id = sq.survey_question_id ".
								" and  sq.survey_id =:survey_id".
								")");
			$stmt->bindParam(':user_id', $user_id);
			$stmt->bindParam(':survey_id', $survey_id);
			$stmt->execute();
			$rows = $stmt->fetchAll();
			foreach($rows as $row)
			{
				$s = new survey_answer($row["survey_answer_id"],$row["survey_question_id"],$row["survey_answer_content"],$row["survey_answer_mark"]);
				$aos[] = $s;
			}

			$dbh->commit();
			$dbh = null;

			return $aos;
		}
		catch(PDOException $e)
		{
			echo '<br />' . $e->getMessage();
			$dbh->rollback();
		}
	}
	function get_survey_answer($id)
	{
		try
		{
			$dbh = new PDO(Constants::$connectionString, Constants::$username, Constants::$password);
			$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$dbh->beginTransaction();

			$stmt = $dbh->prepare("SELECT * FROM  survey_answer s WHERE s.survey_answer_id = :survey_answer_id");
			$stmt->bindParam(':survey_answer_id', $id, PDO::PARAM_INT);
			$stmt->execute();

			$s = null;
			if($row = $stmt->fetch())
			{
				$s = new survey_answer($row["survey_answer_id"],$row["survey_question_id"],$row["survey_answer_content"],$row["survey_answer_mark"]);
			}

			$dbh->commit();
			$dbh = null;
			return $s;
		}
		catch(PDOException $e)
		{
			echo '<br />' . $e->getMessage();
			$dbh->rollback();
		}
	}

	function get_survey_answer_list()
	{
		try
		{
			$dbh = new PDO(Constants::$connectionString, Constants::$username, Constants::$password);
			$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$dbh->beginTransaction();

			$aos = array();
			$stmt = $dbh->query("SELECT * FROM survey_answer");
			$rows = $stmt->fetchAll();
			foreach($rows as $row)
			{
				$s = new survey_answer($row["survey_answer_id"],$row["survey_question_id"],$row["survey_answer_content"],$row["survey_answer_mark"]);
				$aos[] = $s;
			}

			$dbh->commit();
			$dbh = null;

			return $aos;
		}
		catch(PDOException $e)
		{
			echo '<br />' . $e->getMessage();
			$dbh->rollback();
		}
	}

	function insert_survey_answer($s)
	{
		try
		{
			$dbh = new PDO(Constants::$connectionString, Constants::$username, Constants::$password);
			$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$dbh->beginTransaction();

			$stmt = $dbh->prepare("INSERT INTO survey_answer (survey_question_id, survey_answer_content, survey_answer_mark)
							VALUE(:survey_question_id ,:survey_answer_content ,:survey_answer_mark)");


			$stmt->bindParam(':survey_question_id', $s->survey_question_id);
			$stmt->bindParam(':survey_answer_content', $s->survey_answer_content);
			$stmt->bindParam(':survey_answer_mark', $s->survey_answer_mark);

			$stmt->execute();
			$id = $dbh->lastInsertId();
			$dbh->commit();
			$dbh = null;

			return $id;
		}
		catch(PDOException $e)
		{
			echo '<br />' . $e->getMessage();
			$dbh->rollback();
		}
	}

	function update_survey_answer($s)
	{
		try
		{
			$dbh = new PDO(Constants::$connectionString, Constants::$username, Constants::$password);
			$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$dbh->beginTransaction();

			$stmt = $dbh->prepare("UPDATE survey_answer s
						SET 
						s.survey_question_id = :survey_question_id,
						s.survey_answer_content = :survey_answer_content,
						s.survey_answer_mark = :survey_answer_mark
						WHERE 
						s.survey_answer_id = :survey_answer_id");
			$stmt->bindParam(':survey_question_id', $s->survey_question_id);
			$stmt->bindParam(':survey_answer_content', $s->survey_answer_content);
			$stmt->bindParam(':survey_answer_mark', $s->survey_answer_mark);
			$stmt->bindParam(':survey_answer_id', $s->survey_answer_id);

			$stmt->execute();

			$dbh->commit();
			$dbh = null;
		}
		catch(PDOException $e)
		{
			echo '<br />' . $e->getMessage();
			$dbh->rollback();
		}
	}

}
?>